﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_ListRootMenu')
BEGIN
    PRINT 'Dropping Procedure proc_cm_ListRootMenu'
    DROP  Procedure  proc_cm_ListRootMenu
END
GO

PRINT 'Creating Procedure proc_cm_ListRootMenu'
GO

CREATE PROCEDURE [dbo].[proc_cm_ListRootMenu]
	@pUserId UNIQUEIDENTIFIER = NULL
	, @pRootNodeId UNIQUEIDENTIFIER = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

	IF @pRootNodeId IS NULL
		SELECT @pRootNodeId = node_id FROM [dbo].[tbl_sitemap] (NOLOCK) WHERE parent_node_id IS NULL

	SELECT 
		sm.[node_id]
		,sm.[node_title]
		,sm.[node_url]
		,sm.[node_cd]
		,sm.[node_description]
		,sm.[parent_node_id]
		,sm.[role]
		,sm.[display_order_num]
		,sm.[use_generic_page_ind]
		,sm.[generic_page_type]
		,sm.[admin_users]
		,count(child.[node_id]) AS [child_cnt]
	FROM
		[dbo].[tbl_sitemap] sm (NOLOCK)
		left join [dbo].[tbl_sitemap] child (NOLOCK) ON sm.[node_id] = child.[parent_node_id]
	WHERE
		sm.[parent_node_id] = @pRootNodeId
		AND (
			sm.[role] is null or exists (
			SELECT TOP 1 null 
			FROM
				[dbo].[aspnet_UsersInRoles] ur (NOLOCK)
				INNER JOIN [dbo].[aspnet_Roles] r (NOLOCK) ON ur.[RoleId] = r.[RoleId]
			WHERE
				ur.[UserId] = @pUserId
				AND r.[RoleName] = sm.[role]
			)
		)
	GROUP BY
		sm.[node_id]
		,sm.[node_title]
		,sm.[node_url]
		,sm.[node_cd]
		,sm.[node_description]
		,sm.[parent_node_id]
		,sm.[role]
		,sm.[display_order_num]
		,sm.[use_generic_page_ind]
		,sm.[generic_page_type]
		,sm.[admin_users]
	ORDER BY
		sm.[display_order_num]
END
GO

GRANT EXEC ON dbo.proc_cm_ListRootMenu TO PUBLIC
GO

